Shareware Super Platinum 8

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Super Platinum 8 / Shareware Super Platinum 8.iso / mac / WIN_PRO / SPT110.ZIP;1 / SPTDESCR.TXT < prev

Wrap

Text File | 1993-06-18 | 18.4 KB | 439 lines

**************************************************************************** ---------------------------------------------------------------------------- | IMPORTANT - PLEASE READ ---------------------------------------------------------------------------- | | You have a royalty-free right to use, modify, reproduce and distribute | the software found in these sample files provided, however, that: (a) | you agree that the MS software is PROVIDED "AS IS" WITHOUT | WARRANTY OF ANY KIND and in no event shall MS be liable for | any damages whatsoever in connection with your use of the software; | and (b) the utilities are redististributed solely in conjunction with and | as part of your software application. | | Copyright 1993 Microsoft Corporation. All Rights Reserved. | Microsoft and MS-DOS are registered trademarks and Microsoft Access and | Windows are trademarks of Microsoft Corporation. **************************************************************************** Overview -------- The SQL Pass-Through Dynamic Link Library (DLL) provides SQL support beyond that provided by Microsoft Access linked tables. While linked tables cover a wide range of requirements for transparent access to remote data, including binding Microsoft Access form, report and query objects directly to remote tables and views and performing local-remote joins, linked tables don't support such functionality such as executing row returning stored procedures, remote DDL and back end specific SQL. The MSASP110.DLL gives the Microsoft Access Basic programmer programmatic access to these capabilities. Requirements ------------------------- o Microsoft Access o Open Database Connectivity (ODBC) Installation ------------------------- o Copy the MSASP110.DLL file into your Windows\System directory, or into whichever directory your system looks for DLL files. Description of the SQL Pass-Through DLL --------------------------------------- The SQL pass-through interface is a Dynamic Link Library (DLL) that you can call from Access Basic. Its purpose is to allow an Access Basic programmer to pass back end specific (possibly row-returning) SQL strings to an SQL processing back end. In other words, the DLL is simply a link between Microsoft Access and ODBC. ODBC is a specification that defines a call-level interface, or set of function calls, for applications. The interface enables communication between an application and one or more database management systems. Note that MSASP110.DLL doesn't parse the passed string. For example, it doesn't check to see if a called function is actually supported by the back end. The assumption is that users of this DLL will know the type of back end they are connected to as well the specific version of SQL that the back end uses. The Message table (described later in this document) documents any errors. MSASP110.DLL contains four functions: one establishes a connection with a remote database; one terminates that connection; a third performs the SQL pass-through and deals with any returned items or messages; and a fourth returns the last error. This last function is provided in case an error occurs before the Message table can be created. Each of these functions is described later in this document. The MSASP110.DLL creates two types of tables in Microsoft Access: Message tables and Result tables. Message Tables -------------- In the course of establishing links and running SQL and stored procedures, errors and application-specific messages originate from the remote server itself, from Microsoft Access, and from ODBC. MSASP110.DLL inserts the text for errors and messages in a Message table. A specific Message table is defined for each connection. Multiple connections may share the same Message table. Depending on how you set the AppendMsgs argument (described later in this document) when you make an SQL pass-through call, you can either append new messages to the end of the Message table with each call, or purge the Message table with each call. You are responsible for deleting the Message tables when they are no longer needed. Message Table Example --------------------- The column headers are the names of columns in the Message table. Indx Type ErrorNum Class SubClass Descript ---- ----- -------- ------ -------- ------------------------------ 123 1 -1111 Cirrus_err_Cannot_Create_Table 124 2 07 000 Dynamic SQL Error 125 3 SomeDatabaseErrr098: Invalid SQL Syntax 126 4 Stored Procedure Returned Text Indx - Counter that serves as the primary index for the table. Type - Defines the source of the error which could be: Const JET_ERR = 1 ' Microsoft Access-specific error. Const ODBC_ERR = 2 ' ODBC error. Const REMOTE_ERR = 3 ' Error returned by remote database. Const REMOTE_MSG = 4 ' Message returned by SQL stored ' procedure. Const SPT_ERR = 5 ' Message generated by MSASP110 DLL. ErrorNum - Number of the Microsoft Access-specific error; used only for Microsoft Access database engine errors. Class - Class of the ODBC error; used only for ODBC errors. SubClass - Subclass of the ODBC error; used only for ODBC errors. Descript - Additional text for the error or text of the message returned by the stored procedure. Note When an error is a REMOTE_ERR error, the error code and accompanying text appears combined in the Descript field. Result Tables ------------- MSASP110.DLL creates a result table when the back end returns a result set. If the table name doesn't already exist in the Microsoft Access database: MSASP110.DLL creates a Result table in the Microsoft Access database with a structure matching that of the returned table. If the table already exists in the Microsoft Access database: MSASP110.DLL checks to see if the structure of the table matches the structure of the result set returned. If it matches, MSASP110.DLL either purges it and inserts the returned result set or appends new results to the end of the table (depending on the value of the AppendMsgs argument at the time of the MSASP110.DLL call). If the structure doesn't match, MSASP110.DLL returns an error and this (and any further) result sets will be lost. You are responsible for deleting Result tables when they are no longer needed. SPT Application Programming Interface ------------------------------------- Error Codes Each of the MSASP110.DLL functions returns one of six error codes: Const RMT_SUCCESS = 0 ' Operation succeeded with no ' messages or errors. Const RMT_MSGPENDING = 1 ' Operation succeeded with ' messages or non-fatal errors. Const RMT_ERROR = 2 ' Operation failed; there will ' be at least one message in the ' Message table. Const RMT_MSGTABLEERROR = 3 ' Connection to remote database ' failed because the Message ' table couldn't be created or ' because MSASP110.DLL was couldn't write ' to the specified Message table. Const RMT_INVALIDHANDLE = 4 ' RMTQueryExecute or CloseRMTQuery ' functions were called with an ' invalid connection handle (invalid ' source field). Const RMT_MESSAGEINSERTERROR = 5 ' Row couldn't be added to ' Messages table. RMTQueryDef Structure --------------------- The information needed to connect to a back end and maintain the Message tables is stored in a structure: Type RMTQueryDef SQL As String ' SQL statement to execute on back end. ResultDB As String ' Connection string to results database. ResultTable As String ' Base table name for resulting tables. AppendResults As Integer ' Whether to append new result sets to ' existing tables. MsgDB As String ' Connection string to database for ' Message table. MsgTable As String ' Table name for Message table. AppendMsgs As Integer ' Whether to append new errors and ' messages to an existing Message table. Source As Long ' Handle to connection. NumSets As Integer ' Number of result sets returned and ' successfully written to a table from ' last RMTQueryExecute. End Type Note Except for Source (which is set in a call to CreateRMTQueryDef) and NumSets (which is set by each call to RMTQueryExecute), you set each of these values. SQL - SQL statement that will be passed to the SQL back end. No syntax checking is performed; it is passed as-is to the SQL back end. The SQL statement is sent when you call RMTQueryExecute. ResultDB - Valid Microsoft Access database name that specifies the database in which to store the result set; it is in the form: databasename;UID=userid[;PWD=password] PWD is optional; however, omitting it in a secured Microsoft Access database will cause an error. MSASP110.DLL opens the database at the time of the first call to RMTQueryExecute. ResultTable - Name used to create Result tables returned from a remote back end. These tables are created if there are result sets returned as a result of the SQL statement passed in the SQL argument. The first result table will be named by the value contained in ResultTable, and subsequent result sets returned from the same call will be named by using this name as a base, and appending a different number to it for each new table. For example, if ResultTable has a value of "Example", and a call to RMTQueryExecute yields three result sets, they will be named Example, Example2, and Example3, respectively. If tables already exist with the same name and the correct structure, they will be used. The default is "RMTResults". If the value of AppendResults is False (0), the rows in these tables will be purged before placing new data in them. If the value of AppendResults is True (-1), the data will be appended to the end of the appropriate table. If tables exist with the same name and a different structure, an error will be returned and table creation will fail. If a result table contains two or more identically-named columns, it is treated like any multiple-result set. AppendResults - If False (0), the existing data in any results tables will be purged when the table is used by RMTQueryExecute. If True (-1), all results data will be appended to the appropriate tables (see ResultTable, above). The default is False. MsgDB - Same functionality as ResultDB, but used to specify which database is to be used for the message table. MsgTable - Same functionality as ResultTable, but used to specify the name of the Message table. This table remains open until the connection is closed by the CloseRMTQueryDef call. The default is "RMTErrLog". AppendMsgs - Same functionality as AppendResults, but used for the Message table. The default is False. Source - Do not modify this argument except by calling the CreateRMTQueryDef function. NumSets - Do not modify this argument except by calling the RMTQueryExecute function. This argument specifies how many result tables were created by the call. Function Syntax --------------- CreateRMTQueryDef ----------------- Declare CreateRMTQueryDef Lib "MSASP110.DLL" (ConnectString As String, QueryDefinition As RMTQueryDef) As Integer ConnectString - An ODBC connect string specifying the back end that will execute the SQL statement. It is in the form: DSN=datasourcename[;UID=userid];[PWD=password] QueryDefinition - An RMTQueryDef variable. The following fields are the only fields used by this function: MsgDB - (Required) Specifies the database where the Message table will be created. MsgTable - (Required) Specifies the name of the Message that will be created. AppendMsgs - (Optional) Specifies whether to append new errors or messages to an existing Message table. The default is False. Description ----------- This function first attempts to create a Message table in the database specified by the MsgDB field of the RMTQueryDef structure. If this function cannot create the Message table, this function returns RMT_MSG_TABLE_ERROR. This could occur for the following reasons: o The table didn't already exist and couldn't be created. o A table with the same name exists, but wasn't in the correct message table format. o MsgDB doesn't identify a valid database. If a table with the name specified by MsgTable already exists, and is in the correct format, it will be used. If the message table is successfully created, then the function tries to establish the link specified by ConnectString. If this succeeds, the function will fill in the Source field and return either RMT_SUCCESS or RMT_MSGPENDING. If it fails, the reason for the failure will be written into the Message table and the function will return RMT_ERROR. Note If the source field in the structure is non-zero and contains a valid ODBC handle when this call is made, the call will fail. RMTQueryExecute --------------- Declare RMTQueryExecute Lib "MSASP110.DLL" (QueryDefinition As RMTQueryDef) As Integer QueryDefinition - An RMTQueryDef variable. This must be a valid RMTQueryDef variable that was included in a previous call to CreateRMTQueryDef. The following fields are the only ones used by this function: Source - (Required) Must have been filled in with a valid ODBC connection handle by a previous call to CreateRMTQueryDef. SQL - (Required) The SQL statement to be executed by the back end. ResultDB - (Optional) Name of the database for the result sets. Required only if the SQL statement returns rows. ResultTable - (Required) Base name of result set tables. AppendResults - (Optional) Specifies whether to append new results to existing result tables. The default is False. AppendMsgs - (Optional) Specifies whether to append new errors to an existing Message table. The default is False. Description ----------- This function attempts to execute the SQL statement specified by the SQL field on the back end specified by a previous call to RMTCreateQueryDef. Result sets are written to tables named using the ResultTable field with the naming scheme described above. Existing result tables are used if they are in the correct format. New result sets will be appended to the end of existing tables if AppendResults is True; otherwise, existing tables will be purged before writing new results. Any errors will be reported in the Message table specified by a previous call to CreateRMTQueryDef. If the Message table is not accessible for some reason, this function returns RMT_MSG_TABLE_ERROR. New errors will be appended to the end of an existing Message table if AppendMsgs is True; otherwise, the Message table will be purged with each call to RMTQueryExecute. If the call succeeds and there are no messages or errors, RMTQueryExecute returns RMT_SUCCESS. If the call succeeds, but there are messages or errors (written to the Message table), this function returns RMT_MSGPENDING. If the call fails, this function returns RMT_ERROR. CloseRMTQueryDef ---------------- Declare CloseRMTQueryDef Lib "MSASP110.DLL" (QueryDefinition As RMTQueryDef) As Integer QueryDefinition - An RMTQueryDef variable. This must be a valid RMTQueryDef variable that was included in a previous call to CreateRMTQueryDef. The following fields are the only ones used by this function: Source - (Required) ODBC connection handle specifying the connection to be closed. Description ----------- This function closes an ODBC connection, all open Microsoft Access tables used by the connection, and frees all memory associated with the connection. It sets all values in the structure to zero (0) or to empty strings. The Message table is the last thing to be closed. Any errors encountered during this call are written to the Message table before it is closed. If errors are encountered, this function returns RMT_ERROR. If messages are encountered during the closing process, this function returns RMT_MSGPENDING. If MSASP110.DLL could not close the Message table, this function returns RMT_MSG_TABLE_ERROR. If no errors or messages are encountered, this function returns RMT_SUCCESS. RMTError -------- Declare RMTError Lib "MSASP110.DLL" (QueryDefinition As RMTQueryDef, LastMessage As String) As Integer QueryDefinition - An RMTQueryDef variable. This must be a valid RMTQueryDef variable that was included in a previous call to CreateRMTQueryDef. The following fields are the only ones used by this function: Source - (Required) ODBC connection handle specifying the connection to be closed. LastMessage - The text of the last message. Description ----------- This function returns the text of the last error message(s) that occurred. This ability to return error text is important when an error message cannot be logged into the Message table due to some failure (indicated by RMT_MSG_TABLE_ERROR).